﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESMaterialBusiness
    {
        #region 查询二级库库存
        public DataTable GetMaterialStockQty(DataTable dtProductID)
        {
            string strProductIDs = string.Empty;
            foreach (DataRow row in dtProductID.Rows)
            {
                string strProductID = row["ProductID"].ToString();
                strProductIDs += strProductID + ",";
            }

            strProductIDs = strProductIDs.Substring(0, strProductIDs.Length - 1);

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT st.productid,SUM(qty) AS qty");
            strQuery.AppendLine("FROM stockinfo st");
            strQuery.AppendLine(string.Format("WHERE st.productid IN ('{0}')", strProductIDs));
            strQuery.AppendLine("GROUP BY st.productid");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 添加物料申请单
        /// <summary>
        /// 添加物料申请单
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public Boolean AddMaterialAppInfo(Dictionary<string, string> para, DataTable dtMaterial)
        {
            ArrayList SQLStringList = new ArrayList();

            //主表
            string strID = para["MaterialAppInfoID"];
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO materialappinfo(ID,materialappinfoname,dispatchinfoid,containerid,containername,specid,specno,");
            strSQL.AppendLine("    specname,factoryid,processno,oprno,Type,submitemployeeid,submitdate,status,notes,MfgorderName,ProductID,Qty,WorkflowID,apptype)");
            strSQL.AppendLine("VALUES(");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["MaterialAppInfoName"]));
            strSQL.AppendLine(string.Format("'{0}',", para["DispatchInfoID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerName"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecNo"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecName"]));
            strSQL.AppendLine(string.Format("'{0}',", para["FactoryID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProcessNo"]));
            strSQL.AppendLine(string.Format("'{0}',", para["OprNo"]));
            strSQL.AppendLine(string.Format("{0},", para["Type"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SubmitEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["SubmitDate"]));
            strSQL.AppendLine(string.Format("{0},", para["Status"]));
            strSQL.AppendLine(string.Format("'{0}',", para["Notes"]));

            strSQL.AppendLine(string.Format("'{0}',", para["MfgorderName"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProductID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["Qty"]));
            strSQL.AppendLine(string.Format("'{0}',", para["WorkflowID"]));

            strSQL.AppendLine(string.Format("'{0}'", para["AppType"]));
            strSQL.AppendLine(")");

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //物料列表
            if (dtMaterial.Rows.Count > 0)
            {
                foreach (DataRow row in dtMaterial.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO materialapplistinfo(ID,materialappinfoid,productid,qty,uomid,notes,containername)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["ProductID"]));
                    strSQL.AppendLine(string.Format("{0},", row["Qty"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["UOMID"]));
                    strSQL.AppendLine(string.Format("'{0}',", ""));
                    strSQL.AppendLine(string.Format("'{0}'", row["containername"].ToString()));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            //更新派工单状态
            string strDispatchInfoID = para["DispatchInfoID"];
            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("UPDATE dispatchinfo SET status = 25 WHERE id = '{0}'", strDispatchInfoID));

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 获取Product的物料清单
        public DataTable GetMaterialListByProductID(string strProductID, string processno, string strSpecID="")
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendFormat(@"select p.productid,pb2.productname,p.description,pmi.qtyrequired,st.containername,st.qty from product mp

left join bom b on b.bomid=mp.bomid

left join  productmateriallistitem pmi on pmi.bomid=b.bomid

left join productbase pb on pb.productbaseid=pmi.productbaseid

left join product p on p.productid=nvl(pb.revofrcdid,pmi.productid)

left join productbase pb2 on pb2.productbaseid=p.productbaseid

left join submittostockinfo st on st.productname=pb2.productname and st.isfinished=1 and st.processno='{0}'",processno);
            strQuery.AppendLine(string.Format("   WHERE p.productid is not null and  mp.productid = '{0}'", strProductID));

            
            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取已经发料的信息 add:Wangjh
        public DataTable GetMaterialSendByDispatchId(string dispatchId) {
            string strSql = @"select mff.* from materialappinfo mp 
left join materialflinfo mf on mf.materialappinfoid=mp.id
left join materialfllistinfo mff on mff.materialflinfoid=mf.id";

            strSql += $" where mp.dispatchinfoid='{dispatchId}'";

            return OracleHelper.Query(strSql).Tables[0];
        }

        #endregion

        /// <summary>
        /// 获取可代料的批次
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetReplaceMaterialContainer(Dictionary<string,string> para) {
            string strSql = @"select m.processno,c.containerid,c.containername,p.description productdesc,p.productid,wb.workflowname,w.workflowrevision,w.workflowid,c.qty,p.materialname,
p.technicalconditions||'-'||p.materialpaihao||'-'||p.materialguige RawMaterial, ut.attributevalue replacematerial,ut2.attributevalue replacematerialname,ut3.attributevalue replacematerialqty,
cl.containerlevelname,w.processquota 
from container c 
left join containerlevel cl on cl.containerlevelid=c.levelid
left join product p on p.productid=c.productid
left join currentstatus cu on cu.currentstatusid=c.currentstatusid
left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
left join workflow w on w.workflowid=ws.workflowid
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
left join userattribute ut on ut.parentid=c.containerid and ut.userattributename='代料规格型号'
left join userattribute ut2 on ut2.parentid=c.containerid and ut2.userattributename='代料名称'
left join userattribute ut3 on ut3.parentid=c.containerid and ut3.userattributename='代料数'
left join materialappinfo mp on mp.containerid=c.containerid and mp.workflowid=w.workflowid
left join mfgorder m on m.mfgorderid=c.mfgorderid
where c.status=1 and p.bomid is null  ";//and ck.ckattachmentid is null
            strSql += " and (mp.id is not null or m.replacematerial is not null) ";
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"])) {
                strSql += string.Format(" and m.processno like '%{0}%' ",para["ProcessNo"]);
            }
            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += string.Format(" and c.ContainerName like '%{0}%' ", para["ContainerName"]);
            }

            strSql += " order by c.containerid desc ";

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }
        /// <summary>
        /// 保存批次附件
        /// </summary>
        /// <param name="para"></param>
        public void SaveContainerDoc(Dictionary<string, string> para) {
            ArrayList strSqls = new ArrayList();

            string sql = string.Format(@"delete ckattachment where containerid='{0}' and ckattachmentname='{1}'", para["ContainerID"], para["ckattachmentname"]);
            strSqls.Add(sql);

            sql = string.Format(@"INSERT INTO ckattachment(ckattachmentid,ckattachmentname,ckattachtype,cksubmitdate,ckurl,containerid)
values ('{0}','{1}','{2}',sysdate,'{3}','{4}') ", Guid.NewGuid().ToString(), para["ckattachmentname"].ToString(), para["ckattachtype"].ToString(),
para["ckurl"].ToString(), para["ContainerID"].ToString());
            strSqls.Add(sql);
            
            OracleHelper.ExecuteSqlTran(strSqls);
        }

        #region"查询erp接收成功的物料信息"
        public uMESPagingDataDTO GetErpSucceedRecevieData(Dictionary<string, string> para)
        {
            string strSql = @"select ma.containerid,ma.containername ,m.processno,p.description ,replace(ws.workflowstepname,pb.productname||'-','') SpecNameDisp,di.qty,di.plannedcompletiondate,ma.qty ApplyQty,mff.qty ErpSendQty,
pb.productname,nvl( ut.attributevalue,p.technicalconditions||'-'||p.materialpaihao||'-'||p.materialguige) RawMaterial,e.fullname ApplyEmp,ma.submitdate ApplyDate,t.teamname from materialappinfo ma
inner join container c on c.containerid=ma.containerid and c.status!=0
left join mfgorder m on m.mfgordername=ma.mfgordername
left join product p on p.productid=c.productid
left join productbase pb on pb.productbaseid=p.productbaseid
left join dispatchinfo di on di.id=ma.dispatchinfoid--派工信息
left join workflowstep ws on ws.workflowstepid=di.workflowstepid
left join employee e on e.employeeid=ma.submitemployeeid
left join materialflinfo mf on mf.materialappinfoid=ma.id
left join materialfllistinfo mff on mff.materialflinfoid=mf.id
left join userattribute ut on ut.parentid=c.containerid and ut.userattributename='代料规格型号'
left join team t on t.teamid=di.teamid
where ma.apptype=0--零件" + System.Environment.NewLine;
            AddCondition(para, ref strSql);
            strSql += @"union all
select ma.containerid,ma.containername ,m.processno,p.description ,replace(ws.workflowstepname,pb.productname||'-','') SpecNameDisp,di.qty,di.plannedcompletiondate,ma.qty ApplyQty,mff.qty ErpSendQty,
pb2.productname,'' RawMaterial,e.fullname ApplyEmp,ma.submitdate ApplyDate,t.teamname from materialappinfo ma
left join materialapplistinfo ml on ml.materialappinfoid=ma.id
left join product p2 on p2.productid=ml.productid
left join productbase pb2 on pb2.productbaseid=p2.productbaseid
inner join container c on c.containerid=ma.containerid and c.status!=0
left join mfgorder m on m.mfgordername=ma.mfgordername
left join product p on p.productid=c.productid
left join productbase pb on pb.productbaseid=p.productbaseid
left join dispatchinfo di on di.id=ma.dispatchinfoid--派工信息
left join workflowstep ws on ws.workflowstepid=di.workflowstepid
left join employee e on e.employeeid=ma.submitemployeeid
left join materialflinfo mf on mf.materialappinfoid=ma.id--发料信息
left join materialfllistinfo mff on mff.materialflinfoid=mf.id
left join team t on t.teamid=di.teamid
where ma.apptype=1--组件 " + System.Environment.NewLine;
            AddCondition(para, ref strSql);

            strSql = string.Concat(@" select  containerid,containername ,processno,description , SpecNameDisp,qty,plannedcompletiondate,ApplyQty,sum(nvl(ErpSendQty,0)) ErpSendQty,
productname, RawMaterial,ApplyEmp,ApplyDate,teamname from (", strSql, @" )group by  containerid,containername ,processno,description , SpecNameDisp,qty,plannedcompletiondate,ApplyQty,
productname, RawMaterial,ApplyEmp,ApplyDate,teamname
order by ApplyDate desc");

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;

        }

        void AddCondition(Dictionary<string, string> para,ref string strSql) {
            strSql += " and ma.issenderp=5 ";
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += string.Format(" and m.processno like '%{0}%' ", para["ProcessNo"]);
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += string.Format(" and ma.ContainerName like '%{0}%' ", para["ContainerName"]);
            }
            if (para.ContainsKey("StartDate") && !string.IsNullOrWhiteSpace(para["StartDate"]))
            {
                strSql += string.Format("AND di.plannedcompletiondate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]);
            }
            if (para.ContainsKey("EndDate") && !string.IsNullOrWhiteSpace(para["EndDate"]))
            {
                strSql += string.Format("AND di.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]);
            }

            //报工时判断下料工序是否已进行发料
            if (para.ContainsKey("IsSendMaterial"))
            {
                strSql += string.Format(" and ma.ContainerName = '{0}' and  nvl(mff.qty,0)>0", para["CheckContainerName"]);
            }
        }
        #endregion
    }
}
